﻿Imports System.Data.SqlClient

Public Class frmProduct

    Private Sub frmProduct_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            DataGrid.FormatDataGrid(grddata)
            Me.bindData("")
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Public Sub bindData(ByVal search As String)
        Try
            If (search = "") Then
                grddata.DataSource = dbconnection.ExecuteDatatable("select a.ProductId, a.ProductCode, a.ProductName, a.CategoryId, b.CategoryName, b.TypeId, " & _
                "c.TypeName, a.Price, a.MinimumPrice from Product a LEFT JOIN Category b on a.CategoryId = b.CategoryId LEFT JOIN Type c on b.TypeId=c.TypeId where a.Active = 1")
            ElseIf (search <> "") Then
                grddata.DataSource = dbconnection.ExecuteDatatable("select a.ProductId, a.ProductCode, a.ProductName, a.CategoryId, b.CategoryName, b.TypeId, " & _
                "c.TypeName, a.Price, a.MinimumPrice from Product a LEFT JOIN Category b on a.CategoryId = b.CategoryId LEFT JOIN Type c on b.TypeId=c.TypeId where a.Active = 1" & _
                " and UPPER(a.ProductCode+a.ProductName+b.CategoryName) LIKE UPPER(N'%" & search & "%');")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Try
            Me.Close()
        Catch ex As Exception
            MessageBox.Show("Errors: " + ex.ToString)
        End Try
    End Sub

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
        Try
            For Each c As Control In frmMain.pnBody.Controls
                c.Hide()
            Next
            With frmProductAct
                .bindtypetem()
                .bindcatetem(.cbtypeid.SelectedValue)
                .txtProductCode.Focus()
                .lblTitle.Text = "Add Product"
                .lblProductId.Text = ""
                .FormBorderStyle = Windows.Forms.FormBorderStyle.None
                .TopLevel = False
                .Dock = DockStyle.Fill
                frmMain.pnBody.Controls.Add(frmProductAct)
                .Show()
            End With
        Catch ex As Exception
            MessageBox.Show("Errors: " + ex.ToString)
        End Try
    End Sub

    Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
        Try
            If grddata.RowCount <= 0 Then
                Exit Sub
            End If
            For Each c As Control In frmMain.pnBody.Controls
                c.Hide()
            Next
            Dim ed = frmProductAct
            ed.bindtypetem()
            ed.txtProductCode.Focus()
            With grddata.CurrentRow
                ed.lblProductId.Text = .Cells("ProductId").Value
                ed.txtProductCode.Text = .Cells("ProductCode").Value
                ed.txtProductName.Text = .Cells("ProductName").Value
                ed.cbtypeid.SelectedValue = .Cells("TypeId").Value
                ed.bindcatetem(ed.cbtypeid.SelectedValue)
                ed.cbcateid.SelectedValue = .Cells("CategoryId").Value
                ed.txtPrice.Text = .Cells("Price").Value
                ed.txtMinimumPrice.Text = .Cells("MinimumPrice").Value
            End With
            With ed
                .lblTitle.Text = "Edit Product"
                .FormBorderStyle = Windows.Forms.FormBorderStyle.None
                .TopLevel = False
                .Dock = DockStyle.Fill
                frmMain.pnBody.Controls.Add(frmProductAct)
                .Show()
            End With
        Catch ex As Exception
            MessageBox.Show("Errors: " + ex.ToString)
        End Try
    End Sub

    Private Sub txtsearch_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtsearch.TextChanged
        Try
            Me.bindData(txtsearch.Text.Trim)
        Catch ex As Exception
            MessageBox.Show("Errors: " + ex.ToString)
        End Try
    End Sub

    Private Shared Function validdel(ByVal proid As String)
        Try
            Dim delhave = dbconnection.ExecuteScalar("select * from Product a " & _
            " where (exists (select * from Stock b where a.ProductId=b.ProductId) " & _
            " or exists (select * from InvoiceDetail c where a.ProductId=c.ProductId) " & _
            " or exists (select * from PurchaseDetail d where a.ProductId=d.ProductId)) " & _
            " and a.ProductId='" + proid + "';")
            Return delhave
        Catch ex As Exception
            MessageBox.Show("Errors: " + ex.ToString)
            Return False
        End Try
    End Function

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Try
            If grddata.RowCount <= 0 Then
                Exit Sub
            End If
            If (validdel(grddata.CurrentRow.Cells("ProductId").Value) IsNot Nothing) Then
                MessageBox.Show("Errors: this type still active in purchase, invoice, stock please go to remove it first...!")
            Else
                If MessageBox.Show("Do you want to delete this record?", "Delete", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
                    Dim cmddel As SqlCommand
                    cmddel = dbconnection.Connection.CreateCommand
                    With cmddel
                        .CommandType = CommandType.Text
                        .CommandText = "update Product set Active=0 where ProductId=@proid;"
                        .Parameters.Add("@proid", SqlDbType.NVarChar).Value = grddata.CurrentRow.Cells("ProductId").Value
                        .ExecuteNonQuery()
                    End With
                    MessageBox.Show("Data Deleted")
                    Me.bindData("")
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("Errors: " + ex.ToString)
        End Try
    End Sub
End Class